Poverty Rate is greater than or equal to 20% or a household with income less than the federal poverty level of USD 17,050 for a family of four in 2000
At least 500 people or 33% of the population is located more than 1 mile (Urban) and 10 miles (Rural) from the nearest supermarket or grocery store
After visualizing the Food Access Research Atlas 2019 using GIS tool of Geopandas, it was found that in Brooklyn, there are multiple census tracts having greater than or equal to 20% poverty rate which meets condition one but not a single tract meets condition 2 which technically suggests that Brooklyn do not have any Food Desert as per the official definition given by US Department of Agriculture (USDA).
However, Based on multiple articles published by community bloggers, it is found that Central Brooklyn is one of two worst food desert with neighborhoods such as Ocean Hill, Brownsville, and Bedford-Stuyvesant being the lowest income members.
Few such sources are:
The following codes will take a look into variables from different sources and map them over the borough of Brooklyn
Food Access Research Atlas 2019 : Uploaded on Github under Data Collection folder as Kings_data.csv
Grocery Store Database 2017: https://nanda.isr.umich.edu/project/grocery-stores/
### Importing GIS Libraries
import geopandas as gpd
import plotly.graph_objects as go
### Importing data preprocessing & visualization libraries
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import seaborn as sns
### Importing warning library
import warnings
warnings.filterwarnings("ignore")
### Storing New York Map coordinates of 2019 as a pandas dataframe
ny_map = gpd.read_file(r'C:\Users\tuhin\OneDrive\Desktop\Omdena Brooklyn\cb_2019_36_tract_500k.shp', encoding='utf-8')
### Checking how the dataframe looks like
ny_map.head(2)
| STATEFP | COUNTYFP | TRACTCE | AFFGEOID | GEOID | NAME | LSAD | ALAND | AWATER | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36 | 071 | 000502 | 1400000US36071000502 | 36071000502 | 5.02 | CT | 967431 | 969216 | POLYGON ((-74.02226 41.49281, -74.02180 41.496... |
| 1 | 36 | 103 | 135208 | 1400000US36103135208 | 36103135208 | 1352.08 | CT | 2287077 | 0 | POLYGON ((-73.28263 40.83063, -73.28157 40.832... |
### Plotting the map of New York state
fig, ax = plt.subplots(figsize=(20,20))
ny_map.plot(ax=ax)
plt.show()
### Checking if all New York Counties are present in the dataframe
print("The total number of counties present in New York is", ny_map['COUNTYFP'].nunique())
print("Showing all county codes")
ny_map["COUNTYFP"].unique()
The total number of counties present in New York is 62 Showing all county codes
array(['071', '103', '047', '055', '005', '029', '061', '119', '033',
'001', '085', '069', '067', '081', '007', '107', '051', '059',
'073', '015', '063', '053', '065', '083', '027', '117', '009',
'057', '037', '093', '021', '087', '121', '111', '105', '043',
'019', '109', '023', '011', '113', '091', '035', '013', '099',
'075', '039', '089', '045', '101', '095', '003', '025', '049',
'097', '115', '017', '031', '077', '079', '123', '041'],
dtype=object)
## Select "Kings County" which is code - 047 (Brooklyn Borough)
kings_map = ny_map[ny_map["COUNTYFP"]=="047"]
## Checking the column headers of kings dataframe
kings_map.head(5)
| STATEFP | COUNTYFP | TRACTCE | AFFGEOID | GEOID | NAME | LSAD | ALAND | AWATER | geometry | |
|---|---|---|---|---|---|---|---|---|---|---|
| 2 | 36 | 047 | 057800 | 1400000US36047057800 | 36047057800 | 578 | CT | 172233 | 0 | POLYGON ((-73.95398 40.60140, -73.95304 40.601... |
| 3 | 36 | 047 | 058900 | 1400000US36047058900 | 36047058900 | 589 | CT | 424025 | 38353 | POLYGON ((-73.94605 40.72926, -73.94419 40.729... |
| 18 | 36 | 047 | 037000 | 1400000US36047037000 | 36047037000 | 370 | CT | 336242 | 0 | POLYGON ((-73.96615 40.58667, -73.96564 40.588... |
| 19 | 36 | 047 | 019900 | 1400000US36047019900 | 36047019900 | 199 | CT | 188914 | 0 | POLYGON ((-73.96949 40.68629, -73.96749 40.686... |
| 26 | 36 | 047 | 040000 | 1400000US36047040000 | 36047040000 | 400 | CT | 192000 | 0 | POLYGON ((-73.98078 40.59845, -73.97986 40.598... |
### Reading the Food Access Research Atlas of 2019
kings_foodaccess = pd.read_csv('kings_data.csv')
kings_foodaccess.head(2)
| CensusTract | Track Code | State | County | Urban | Pop2010 | OHU2010 | GroupQuartersFlag | NUMGQTRS | PCTGQTRS | ... | TractSeniors | TractWhite | TractBlack | TractAsian | TractNHOPI | TractAIAN | TractOMultir | TractHispanic | TractHUNV | TractSNAP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36047000100 | 100 | New York | Kings County | 1 | 4338 | 2227 | 0 | 424 | 9.77 | ... | 843 | 3396 | 427 | 330 | 0 | 15 | 170 | 347 | 1417 | 126 |
| 1 | 36047000200 | 200 | New York | Kings County | 1 | 1470 | 360 | 0 | 39 | 2.65 | ... | 70 | 530 | 103 | 49 | 0 | 16 | 772 | 1277 | 265 | 138 |
2 rows × 148 columns
### Imputing leading zeroes which usually gets removed when reading a csv file
kings_foodaccess['Track Code'] = kings_foodaccess['Track Code'].astype(str)
kings_foodaccess['Track Code'] = kings_foodaccess['Track Code'].str.zfill(6)
kings_foodaccess.head(2)
| CensusTract | Track Code | State | County | Urban | Pop2010 | OHU2010 | GroupQuartersFlag | NUMGQTRS | PCTGQTRS | ... | TractSeniors | TractWhite | TractBlack | TractAsian | TractNHOPI | TractAIAN | TractOMultir | TractHispanic | TractHUNV | TractSNAP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36047000100 | 000100 | New York | Kings County | 1 | 4338 | 2227 | 0 | 424 | 9.77 | ... | 843 | 3396 | 427 | 330 | 0 | 15 | 170 | 347 | 1417 | 126 |
| 1 | 36047000200 | 000200 | New York | Kings County | 1 | 1470 | 360 | 0 | 39 | 2.65 | ... | 70 | 530 | 103 | 49 | 0 | 16 | 772 | 1277 | 265 | 138 |
2 rows × 148 columns
### TRACTCE will be the primary key to link all the database.
## Renaming Tract Code as "TRACTCE"
kings_foodaccess = kings_foodaccess.rename(columns={'Track Code': 'TRACTCE'})
kings_foodaccess.head(2)
| CensusTract | TRACTCE | State | County | Urban | Pop2010 | OHU2010 | GroupQuartersFlag | NUMGQTRS | PCTGQTRS | ... | TractSeniors | TractWhite | TractBlack | TractAsian | TractNHOPI | TractAIAN | TractOMultir | TractHispanic | TractHUNV | TractSNAP | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36047000100 | 000100 | New York | Kings County | 1 | 4338 | 2227 | 0 | 424 | 9.77 | ... | 843 | 3396 | 427 | 330 | 0 | 15 | 170 | 347 | 1417 | 126 |
| 1 | 36047000200 | 000200 | New York | Kings County | 1 | 1470 | 360 | 0 | 39 | 2.65 | ... | 70 | 530 | 103 | 49 | 0 | 16 | 772 | 1277 | 265 | 138 |
2 rows × 148 columns
### We need to map TRACTCE in kings database and TRACTCE in Food Atlas 2019 database to create a single database
first_compiled = kings_map.merge(kings_foodaccess, on='TRACTCE')
### Checking the total number of census tracks present in Kings County (Brooklyn Borough)
first_compiled['TRACTCE'].nunique()
754
### Visualizing the Brooklyn Borough with Poverty Rate as one of the key metric
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=first_compiled.geometry.__geo_interface__,
locations=first_compiled.index,
z=first_compiled['PovertyRate'],
colorscale='OrRd',
zmin=first_compiled['PovertyRate'].min(),
zmax=first_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover option Over Text
hover_text = []
for idx, row in first_compiled.iterrows():
hover_text.append(f"Census Tract Area: {row['TRACTCE']}<br>PovertyRate: {row['PovertyRate']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": first_compiled.geometry.centroid.y.mean(),
"lon": first_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='Poverty Rate')
fig.show()
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=first_compiled.geometry.__geo_interface__,
locations=first_compiled.index,
z=first_compiled['LILATracts_halfAnd10'],
colorscale='OrRd',
zmin=first_compiled['LILATracts_halfAnd10'].min(),
zmax=first_compiled['LILATracts_halfAnd10'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in first_compiled.iterrows():
hover_text.append(f"Census Tract Area: {row['TRACTCE']}<br>Low Income & Low Access - 1/2 mile: {row['PovertyRate']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": first_compiled.geometry.centroid.y.mean(), "lon": first_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='LILATracts_halfAnd10')
fig.show()
This database contains the following
2017 Grocery data was used as a proxy to 2019 Food Access Research Atlas data
grocery_data = pd.read_csv('Grocery store database.csv')
grocery_data.head(2)
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | aden_emp_4452 | count_452311 | count_sales_452311 | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 1001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 31 columns
grocery_data['tract_fips10'] = grocery_data['tract_fips10'].astype(str)
grocery_data['tract_fips10'] = grocery_data['tract_fips10'].str.zfill(11)
grocery_data.head(2)
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | aden_emp_4452 | count_452311 | count_sales_452311 | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 01001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 31 columns
# Extracting portions
grocery_data['State_Code'] = grocery_data['tract_fips10'].str[:2]
grocery_data['County_Code'] = grocery_data['tract_fips10'].str[2:5]
grocery_data['Census_Tract_Code'] = grocery_data['tract_fips10'].str[-6:]
grocery_data.head(2)
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 01 | 001 | 020100 |
| 1 | 01001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 01 | 001 | 020100 |
2 rows × 34 columns
### Selecting New York State (State Code = 36)
grocery_ny = grocery_data[grocery_data["State_Code"]=="36"]
### Selecting Kings County (county code = 047)
grocery_kings = grocery_ny[grocery_ny['County_Code'] == '047']
grocery_kings
| tract_fips10 | year | population | aland10 | count_445110 | count_sales_445110 | count_emp_445110 | popden_445110 | popden_sales_445110 | popden_emp_445110 | ... | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 620074 | 36047000100 | 2003 | 5254.272 | 0.08627 | 4 | 4 | 4 | 0.761285 | 0.761285 | 0.761285 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620075 | 36047000100 | 2004 | 5164.090 | 0.08627 | 4 | 4 | 4 | 0.774580 | 0.774580 | 0.774580 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620076 | 36047000100 | 2005 | 5073.908 | 0.08627 | 3 | 3 | 3 | 0.591260 | 0.591260 | 0.591260 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620077 | 36047000100 | 2006 | 4983.727 | 0.08627 | 3 | 2 | 3 | 0.601959 | 0.401306 | 0.601959 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| 620078 | 36047000100 | 2007 | 4893.545 | 0.08627 | 4 | 3 | 4 | 0.817403 | 0.613053 | 0.817403 | ... | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 | 000100 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 630723 | 36047990100 | 2012 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630724 | 36047990100 | 2013 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630725 | 36047990100 | 2014 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630726 | 36047990100 | 2015 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
| 630727 | 36047990100 | 2017 | 0.000 | 0.00000 | 0 | 0 | 0 | NaN | NaN | NaN | ... | 0 | NaN | NaN | NaN | NaN | NaN | NaN | 36 | 047 | 990100 |
10654 rows × 34 columns
### TRACTCE will be the primary key to link all the database.
## Renaming Census Tract Code as "TRACTCE"
grocery_kings = grocery_kings.rename(columns={'Census_Tract_Code': 'TRACTCE'})
### Selecting only 2017 data
grocery_kings_17 = grocery_kings[grocery_kings['year'] == 2017]
grocery_kings_17['TRACTCE'].nunique()
761
### We need to map TRACTCE in first_compiled and TRACTCE in grocery database to create a single database
second_compiled = first_compiled.merge(grocery_kings_17, on='TRACTCE')
second_compiled.head(2)
| STATEFP | COUNTYFP | TRACTCE | AFFGEOID | GEOID | NAME | LSAD | ALAND | AWATER | geometry | ... | count_sales_452311 | count_emp_452311 | popden_452311 | popden_sales_452311 | popden_emp_452311 | aden_452311 | aden_sales_452311 | aden_emp_452311 | State_Code | County_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 36 | 047 | 057800 | 1400000US36047057800 | 36047057800 | 578 | CT | 172233 | 0 | POLYGON ((-73.95398 40.60140, -73.95304 40.601... | ... | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 |
| 1 | 36 | 047 | 058900 | 1400000US36047058900 | 36047058900 | 589 | CT | 424025 | 38353 | POLYGON ((-73.94605 40.72926, -73.94419 40.729... | ... | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 36 | 047 |
2 rows × 190 columns
second_compiled.info()
<class 'geopandas.geodataframe.GeoDataFrame'> Int64Index: 754 entries, 0 to 753 Columns: 190 entries, STATEFP to County_Code dtypes: float64(133), geometry(1), int64(44), object(12) memory usage: 1.1+ MB
socio = pd.read_csv('socio_eco_data.csv')
socio.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | POWNOC16_20 | AFFLUENCE16_20 | DISADVANTAGE16_20 | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1001020100 | 3.793571 | 1941 | 511.6551209 | 0.017001545 | 0.774858296 | 0.121071614 | 0.002060793 | 0 | 0.142857149 | ... | 0.731601715 | 0.256776839 | 0.171104908 | 0.006354113 | 70699 | 71324 | 75104 | 0.949669778 | ||
| 1 | 1001020200 | 1.282175 | 1757 | 1370.327881 | 0.017074559 | 0.364826411 | 0.583949924 | 0.00284576 | 0 | 0.105864435 | ... | 0.684118688 | 0.224515289 | 0.208804905 | 0.006640106 | 50133 | 83750 | 47604 | 1.759305954 |
2 rows × 39 columns
socio['TRACT_FIPS20'] = socio['TRACT_FIPS20'].astype(str)
socio['TRACT_FIPS20'] = socio['TRACT_FIPS20'].str.zfill(11)
socio.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | POWNOC16_20 | AFFLUENCE16_20 | DISADVANTAGE16_20 | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 3.793571 | 1941 | 511.6551209 | 0.017001545 | 0.774858296 | 0.121071614 | 0.002060793 | 0 | 0.142857149 | ... | 0.731601715 | 0.256776839 | 0.171104908 | 0.006354113 | 70699 | 71324 | 75104 | 0.949669778 | ||
| 1 | 01001020200 | 1.282175 | 1757 | 1370.327881 | 0.017074559 | 0.364826411 | 0.583949924 | 0.00284576 | 0 | 0.105864435 | ... | 0.684118688 | 0.224515289 | 0.208804905 | 0.006640106 | 50133 | 83750 | 47604 | 1.759305954 |
2 rows × 39 columns
# Extracting portions
socio['State_Code'] = socio['TRACT_FIPS20'].str[:2]
socio['County_Code'] = socio['TRACT_FIPS20'].str[2:5]
socio['Census_Tract_Code'] = socio['TRACT_FIPS20'].str[-6:]
socio.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 3.793571 | 1941 | 511.6551209 | 0.017001545 | 0.774858296 | 0.121071614 | 0.002060793 | 0 | 0.142857149 | ... | 0.006354113 | 70699 | 71324 | 75104 | 0.949669778 | 01 | 001 | 020100 | ||
| 1 | 01001020200 | 1.282175 | 1757 | 1370.327881 | 0.017074559 | 0.364826411 | 0.583949924 | 0.00284576 | 0 | 0.105864435 | ... | 0.006640106 | 50133 | 83750 | 47604 | 1.759305954 | 01 | 001 | 020200 |
2 rows × 42 columns
### Selecting New York State (State Code = 36)
socio_ny = socio[socio["State_Code"]=="36"]
### Selecting Kings County (county code = 047)
socio_kings = socio_ny[socio_ny['County_Code'] == '047']
socio_kings.head(2)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | State_Code | County_Code | Census_Tract_Code | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 50941 | 36047000100 | 0.080239 | 4539 | 56568.24609 | 0.069839172 | 0.645296335 | 0.146948665 | 0.124476761 | 0.022790698 | 0.003593145 | ... | 0.072368875 | 195292 | 228307 | 36 | 047 | 000100 | ||||
| 50942 | 36047000200 | 0.106923 | 992 | 9277.713867 | 0.73286289 | 0.164314523 | 0.021169355 | 0.353830636 | 0.273565561 | 0.37416777 | ... | 0.453419685 | 36 | 047 | 000200 |
2 rows × 42 columns
socio_kings = socio_kings.rename(columns={'Census_Tract_Code': 'TRACTCE'})
socio_kings.head(1)
| TRACT_FIPS20 | ALAND20 | TOTPOP20 | POPDEN16_20 | PHISPANIC16_20 | PNHWHITE16_20 | PNHBLACK16_20 | PFBORN16_20 | PLIMENG16_20 | PED1_16_20 | ... | HISPAN_FORBORN_LIMENG16_20 | MEDFAMINC16_20 | MEDFAMINC_NHWHITE16_20 | MEDFAMINC_BLACK16_20 | MEDFAMINC_HISPANIC16_20 | RATIO_MEDFAMINC_NHWTOB16_20 | RATIO_MEDFAMINC_NHWTOHISP16_20 | State_Code | County_Code | TRACTCE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 50941 | 36047000100 | 0.080239 | 4539 | 56568.24609 | 0.069839172 | 0.645296335 | 0.146948665 | 0.124476761 | 0.022790698 | 0.003593145 | ... | 0.072368875 | 195292 | 228307 | 36 | 047 | 000100 |
1 rows × 42 columns
socio_kings['TRACTCE'].nunique()
805
third_compiled = pd.merge(second_compiled, socio_kings, on='TRACTCE', how='left')
third_compiled.info()
<class 'geopandas.geodataframe.GeoDataFrame'> Int64Index: 754 entries, 0 to 753 Columns: 231 entries, STATEFP to County_Code_y dtypes: float64(135), geometry(1), int64(44), object(51) memory usage: 1.3+ MB
This database contains the following
2017 Eating & Drinking Data is used as a proxy to 2019 Food Access Research Atlas data
eating = pd.read_csv('eating.csv')
eating.head(2)
| tract_fips10 | year | population | aland10 | count_7225 | count_sales_7225 | count_emp_7225 | popden_7225 | popden_sales_7225 | popden_emp_7225 | ... | aden_emp_722515 | count_722410 | count_sales_722410 | count_emp_722410 | popden_722410 | popden_sales_722410 | popden_emp_722410 | aden_722410 | aden_sales_722410 | aden_emp_722410 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 1001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 49 columns
eating['tract_fips10'] = eating['tract_fips10'].astype(str)
eating['tract_fips10'] = eating['tract_fips10'].str.zfill(11)
eating.head(2)
| tract_fips10 | year | population | aland10 | count_7225 | count_sales_7225 | count_emp_7225 | popden_7225 | popden_sales_7225 | popden_emp_7225 | ... | aden_emp_722515 | count_722410 | count_sales_722410 | count_emp_722410 | popden_722410 | popden_sales_722410 | popden_emp_722410 | aden_722410 | aden_sales_722410 | aden_emp_722410 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 01001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 49 columns
# Extracting portions
eating['State_Code'] = eating['tract_fips10'].str[:2]
eating['County_Code'] = eating['tract_fips10'].str[2:5]
eating['TRACTCE'] = eating['tract_fips10'].str[-6:]
### Selecting New York State (State Code = 36)
eating_ny = eating[eating["State_Code"]=="36"]
### Selecting Kings County (county code = 047)
eating_kings = eating_ny[eating_ny['County_Code'] == '047']
eating_kings_17 = eating_kings[eating_kings['year'] == 2017]
fourth_compiled = pd.merge(third_compiled, eating_kings_17, on='TRACTCE', how='left')
fourth_compiled.info()
<class 'geopandas.geodataframe.GeoDataFrame'> Int64Index: 754 entries, 0 to 753 Columns: 282 entries, STATEFP to County_Code dtypes: float64(167), geometry(1), int64(60), object(54) memory usage: 1.6+ MB
This database contains the following
2017 Convenience stores, Liqour and Tobacco database is used as a proxy to 2019 Food Access Research Atlas data
convenience = pd.read_csv('liquor_convenience_stores.csv')
convenience.head(2)
| tract_fips10 | year | population | aland10 | count_4453 | count_sales_4453 | count_emp_4453 | popden_4453 | popden_sales_4453 | popden_emp_4453 | ... | aden_emp_445120 | count_447110 | count_sales_447110 | count_emp_447110 | popden_447110 | popden_sales_447110 | popden_emp_447110 | aden_447110 | aden_sales_447110 | aden_emp_447110 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 1001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 40 columns
convenience['tract_fips10'] = convenience['tract_fips10'].astype(str)
convenience['tract_fips10'] = convenience['tract_fips10'].str.zfill(11)
convenience.head(2)
| tract_fips10 | year | population | aland10 | count_4453 | count_sales_4453 | count_emp_4453 | popden_4453 | popden_sales_4453 | popden_emp_4453 | ... | aden_emp_445120 | count_447110 | count_sales_447110 | count_emp_447110 | popden_447110 | popden_sales_447110 | popden_emp_447110 | aden_447110 | aden_sales_447110 | aden_emp_447110 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 01001020100 | 2003 | 1858.883 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
| 1 | 01001020100 | 2004 | 1852.185 | 3.787641 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0 | 0 | 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2 rows × 40 columns
# Extracting portions
convenience['State_Code'] = convenience['tract_fips10'].str[:2]
convenience['County_Code'] = convenience['tract_fips10'].str[2:5]
convenience['TRACTCE'] = convenience['tract_fips10'].str[-6:]
### Selecting New York State (State Code = 36)
convenience_ny = convenience[convenience["State_Code"]=="36"]
### Selecting Kings County (county code = 047)
convenience_kings = convenience_ny[convenience_ny['County_Code'] == '047']
convenience_kings_17 = convenience_kings[convenience_kings['year'] == 2017]
fifth_compiled = pd.merge(fourth_compiled, convenience_kings_17, on='TRACTCE', how='left')
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fifth_compiled.geometry.__geo_interface__,
locations=fifth_compiled.index,
z=fifth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fifth_compiled['PovertyRate'].min(),
zmax=fifth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fifth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Population (2010): {row['Pop2010']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br># of liqour stores: {row['count_4453']}<br># of tobacco stores: {row['count_453991']}<br># of convenience stores: {row['count_445120']}<br># of gas station convenience stores: {row['count_447110']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fifth_compiled.geometry.centroid.y.mean(),
"lon": fifth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()
convenience.info()
<class 'pandas.core.frame.DataFrame'> RangeIndex: 1036462 entries, 0 to 1036461 Data columns (total 43 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 tract_fips10 1036462 non-null object 1 year 1036462 non-null int64 2 population 1020881 non-null float64 3 aland10 1022434 non-null float64 4 count_4453 1036462 non-null int64 5 count_sales_4453 1036462 non-null int64 6 count_emp_4453 1036462 non-null int64 7 popden_4453 1015188 non-null float64 8 popden_sales_4453 1015178 non-null float64 9 popden_emp_4453 1015181 non-null float64 10 aden_4453 1017996 non-null float64 11 aden_sales_4453 1018000 non-null float64 12 aden_emp_4453 1017997 non-null float64 13 count_453991 1036462 non-null int64 14 count_sales_453991 1036462 non-null int64 15 count_emp_453991 1036462 non-null int64 16 popden_453991 1015088 non-null float64 17 popden_sales_453991 1015082 non-null float64 18 popden_emp_453991 1015080 non-null float64 19 aden_453991 1017996 non-null float64 20 aden_sales_453991 1017996 non-null float64 21 aden_emp_453991 1017996 non-null float64 22 count_445120 1036462 non-null int64 23 count_sales_445120 1036462 non-null int64 24 count_emp_445120 1036462 non-null int64 25 popden_445120 1015300 non-null float64 26 popden_sales_445120 1015285 non-null float64 27 popden_emp_445120 1015293 non-null float64 28 aden_445120 1017996 non-null float64 29 aden_sales_445120 1017996 non-null float64 30 aden_emp_445120 1017996 non-null float64 31 count_447110 1036462 non-null int64 32 count_sales_447110 1036462 non-null int64 33 count_emp_447110 1036462 non-null int64 34 popden_447110 1015047 non-null float64 35 popden_sales_447110 1015047 non-null float64 36 popden_emp_447110 1015047 non-null float64 37 aden_447110 1017996 non-null float64 38 aden_sales_447110 1017996 non-null float64 39 aden_emp_447110 1017996 non-null float64 40 State_Code 1036462 non-null object 41 County_Code 1036462 non-null object 42 TRACTCE 1036462 non-null object dtypes: float64(26), int64(13), object(4) memory usage: 340.0+ MB
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fifth_compiled.geometry.__geo_interface__,
locations=fifth_compiled.index,
z=fifth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fifth_compiled['PovertyRate'].min(),
zmax=fifth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fifth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Population (2010): {row['Pop2010']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>Supermarkets: {row['count_445110']}<br>Speciality Stores: {row['count_4452']}<br>Warehouse Clubs/Supercentres: {row['count_452311']}<br>Fast Food Restaurants: {row['count_722513']}<br>Full Service Restaurants: {row['count_722511']}<br>Coffee Shops: {row['count_722515']}<br>Bars: {row['count_722410']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fifth_compiled.geometry.centroid.y.mean(),
"lon": fifth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fifth_compiled.geometry.__geo_interface__,
locations=fifth_compiled.index,
z=fifth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fifth_compiled['PovertyRate'].min(),
zmax=fifth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fifth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Employed with Professional Studies (Prop): {row['PPROF16_20']}<br>Households w public assist income/food stamps (%): {row['PPUBAS16_20']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fifth_compiled.geometry.centroid.y.mean(),
"lon": fifth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fifth_compiled.geometry.__geo_interface__,
locations=fifth_compiled.index,
z=fifth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fifth_compiled['PovertyRate'].min(),
zmax=fifth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fifth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Female-headed families with kids (Prop): {row['PFHFAM16_20']}<br>Single-parent families with kids (Prop): {row['PSNGPNT16_20']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fifth_compiled.geometry.centroid.y.mean(),
"lon": fifth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()
The socio-economic database has two important metrics to measure the level of socio-economic equity
The Affluence factor is the average of three variables given below
The Disadvantages factor is the average of three variables given below
fig = go.Figure()
# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fifth_compiled.geometry.__geo_interface__,
locations=fifth_compiled.index,
z=fifth_compiled['PovertyRate'],
colorscale='OrRd',
zmin=fifth_compiled['PovertyRate'].min(),
zmax=fifth_compiled['PovertyRate'].max(),
marker_opacity=0.5,
marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fifth_compiled.iterrows():
hover_text.append(
f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Disadvantage: {row['DISADVANTAGE16_20']}<br>Affluence: {row['AFFLUENCE16_20']}")
fig.update_traces(hoverinfo='text', text=hover_text)
fig.update_layout(mapbox_style="carto-positron",
mapbox_zoom=8,
mapbox_center={"lat": fifth_compiled.geometry.centroid.y.mean(),
"lon": fifth_compiled.geometry.centroid.x.mean()},
margin={"r":0,"t":0,"l":0,"b":0},
hovermode='closest',
title='PovertyRate')
fig.show()